<?php

App::import('Sanitize');
class MassswapController extends AppController {

	var $name = 'Massswap';
	var $helpers = array('Html','Form','Ajax','Javascript');
    var $layout = 'default';
    var $pageTitle = 'Mass Swap Helper';
    var $components = array('RequestHandler','Functions');

	function beforeRender() {
		$groupid = $this->Auth->user('group_id');
		$name = 'Massswap';
		IF($groupid != 1){
			$result1 = mysql_query("SELECT id FROM applications WHERE name = '$name'");
			$appid = mysql_fetch_object($result1)->id;
			$result2 = mysql_query("SELECT count(*) as count FROM groupshasrights WHERE group_id = '$groupid' AND application_id = '$appid'");
			$count = mysql_fetch_object($result2)->count;
			IF($count == 0){
				$this->Session->setFlash(__('Access Denied', true));
				$this->redirect(array('controller' => 'pages' ,'action' => 'error'));	
			}
		}
	}
	
	function dmperror(){
		
	}
    
	// Create new Run
	function step1(){
	    
		// Save data from Form
		if (!empty($this->data)) {
	    	
			$this->data = Sanitize::clean($this->data, array('encode' => false)); //clean the array

			$project = $this->data['Massswap']['projectid'];
			mysql_query("INSERT INTO `mmst_helper`.`runs` (`id`, `fileid`, `batchid`, `project_id`) VALUES (NULL, NULL, NULL,$project)");
	    	$runid = mysql_insert_id();
			if($runid != NULL) {
				mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '1', NOW())");
				$this->Session->setFlash(__('The run has been saved', true));
				$this->redirect(array('controller' => 'massswap', 'action' => 'step2', '?' => array('runid' => $runid)));
			} 
			else {
				$this->Session->setFlash(__('The run could not be saved. Please, try again.', true));
			}
		}  	
	}

	
	// Load MAC's
	function step2(){
	    
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
	    $this->set(compact('runid'));
		
	    // check if step is already passed
	    if(($this->Functions->checkstatus($runid,2) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step3', '?' => array('runid' => $runid)));
	    }
	    else {
	    	// Save data from Form
	    	if (!empty($this->data)) {
	    		$this->data = Sanitize::clean($this->data, array('encode' => false)); //clean the array
	    		$runid = $this->data['Massswap']['runid'];
	    		$daten = $this->data['Massswap']['macs'];
	    		$rows = explode("\\n",$daten);
				
	    		// create array with service types
				$servicetype = array(); $y = 0;
				$resultservice = mysql_query("SELECT * FROM `mmst_helper`.`macservicetypes`");
				while($row = mysql_fetch_array($resultservice)){
					$servicetype[$y]['id'] = $row["id"];	
					$servicetype[$y]['name'] = $row["name"];	
					$y++;	
				}
				
	    		// find service -> $z1 = searchstring, $z2 = servicetypearray
				function servicefinder($z1,$z2){
					$count = count($z2);
					$a = 0;
					while($a < $count){
			    		if($z2[$a]['name'] == $z1) { return $z2[$a]['id']; }	
			    		$a++;
					}		
				}
				
	    	
				foreach($rows as $r){
			    	if(strlen($r) > 5){
			    		$det = explode(";",$r);
			    		$mac = $det['0'];
			    		$servicename = $det['1'];
			    		$macserviceid = servicefinder($servicename,$servicetype);
			    		$macstatus = 24; // -> picked
			    		$mactype = 1; // -> unknown
				    	mysql_query("INSERT INTO `mmst_helper`.`macs` (`id`, `mac`, `status_id`, `mactype_id`, `macservicetype_id`) VALUES (NULL, '$mac', '$macstatus', '$mactype', '$macserviceid')");	
				    	$macid = mysql_insert_id();
				    	mysql_query("INSERT INTO `mmst_helper`.`runshasmacs` (`id`, `run_id`, `mac_id`) VALUES (NULL, '$runid', '$macid')");
			    	}
				}
				unset($m);
				
				mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '2', NOW())");
				$this->Session->setFlash(__('The MACs has been saved', true));
				$this->redirect(array('controller' => 'massswap', 'action' => 'step3', '?' => array('runid' => $runid)));
			} 
			else {
				$this->Session->setFlash(__('The MACs could not be saved. Please, try again.', true));
			}
	    }  	
	} 
	
	
	// Load Detaildata File
	function step3(){

		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
	    $this->set(compact('runid'));
	    
	     // check if step is already passed
	    if(($this->Functions->checkstatus($runid,3) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step4', '?' => array('runid' => $runid)));
	    }
	    else {
	    	if (!empty($this->data) && is_uploaded_file($this->data['Myfile']['upload']['tmp_name'])) {
            	$fileData = fread(fopen($this->data['Myfile']['upload']['tmp_name'], "r"),$this->data['Myfile']['upload']['size']);
            
            	$this->data['Myfile']['name'] = $this->data['Myfile']['upload']['name'];
            	$this->data['Myfile']['type'] = $this->data['Myfile']['upload']['type'];
            	$this->data['Myfile']['size'] = $this->data['Myfile']['upload']['size'];
            	$this->data['Myfile']['data'] = $fileData;

            	$this->loadModel('Myfile');
            	$this->Myfile->save($this->data);
            	$fileid = $this->Myfile->id;
          
            	mysql_query("INSERT INTO `mmst_helper`.`runshasfiles` (`id`, `run_id`, `file_id`) VALUES (NULL, '$runid', '$fileid')");
	        	$this->Session->setFlash(__('Fileuplado has been saved', true));
            	$this->redirect(array('controller' => 'massswap', 'action' => 'step3action', '?' => array('runid' => $runid, 'fileid' => $fileid)));
	    	}
			else {
		    	$this->Session->setFlash(__('Uploaded file could not be saved. Please, try again.', true));
			}
	    }
	} 
	
	
	// Splitt uploaded file into tables and make relational database entries
	function step3action(){
	    
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		$fileid = $this->params['url']['fileid'];
		
		$this->loadModel('Myfile');
		$this->data = $this->Myfile->findById($fileid);
		$filedata = $this->data['Myfile']['data'];
		$rows = explode("\n",$filedata);
		$count = count($rows);
		
		// create array with modem types
		$macstype = array(); $y = 0;
		$resultmacs = mysql_query("SELECT * FROM `mmst_helper`.`mactypes`");
		while($rowmacs = mysql_fetch_array($resultmacs)){
			$macstype[$y]['id'] = $rowmacs["id"];	
			$macstype[$y]['partnum'] = $rowmacs["partnum"];	
			$macstype[$y]['description'] = $rowmacs["description"];	
			$macstype[$y]['return'] = $rowmacs["return"];
			$y++;	
		}
		
		// create array with products
		function createdproductarray(){
			$products = array(); $y = 0;
			$resultproducts = mysql_query("SELECT * FROM `mmst_helper`.`products`");
			while($rowproducts = mysql_fetch_array($resultproducts)){
				$products[$y]['id'] = $rowproducts["id"];
				$products[$y]['instance'] = $rowproducts["instance"];	
				$products[$y]['service'] = $rowproducts["service"];	
				$products[$y]['family'] = $rowproducts["family"];	
				$products[$y]['partnumber'] = $rowproducts["partnumber"];
				$products[$y]['partdescription'] = $rowproducts["partdescription"];
				$y++;	
			}
			return $products;
		}
		
		// find mac type $z1 = searchstring, $z2 = modemtypearray
		function mactypefinder($z1,$z2){
			$count = count($z2);
			$a = 0;
			//$back = 0;
			while($a < $count){
			    if($z2[$a]['description'] == $z1) { return $z2[$a]['id']; }	
			    $a++;
			}
			//return $back;	
		}
		
		
		// find product $u1 = searchstring, $u2 = productarray
		function productfinder($u1,$u2){
			$count = count($u2);
			$a = 0;
			$back = 0;
			while($a < $count){
			    if($u2[$a]['partnumber'] == $u1) { $back = $u2[$a]['id']; }	
			    $a++;	
			}
			return $back;	
		}
		
		
		$x = 1; // first line is the header -> skipping
		while($x < $count) {
			$colums = explode(";",$rows[$x]); 
			
			// Insert Customer data
			if($colums[0] == "CUSTOMER INFO"){ 
				$search = "NO CUSTOMER INFORMATION FOUND";
				if(strpos($colums[2],$search) === false){
				    $colums[6] = mysql_real_escape_string($colums[6]);
					$colums[7] = mysql_real_escape_string($colums[7]);
					$colums[15] = mysql_real_escape_string($colums[15]);
					$colums[18] = mysql_real_escape_string($colums[18]);
					$colums[20] = mysql_real_escape_string($colums[20]);
					$colums[23] = mysql_real_escape_string($colums[23]);
				    $colums[25] = mysql_real_escape_string($colums[25]);
					$sql = "INSERT INTO `mmst_helper`.`customers` (`id`, `mac_address`, `modem_type`, `serial_no`, `account_number`, `inst_first_name`, `inst_last_name`, `inst_mid_name`, `salutation`, `language`, `contact_role`, `phone1`, `phone2`, `credit_rating`, `inst_street`, `inst_house_no`, `inst_zip`, `inst_city`, `collection`, `bill_street`, `bill_house_no`, `bill_zip`, `bill_city`, `po_box`, `company_name`) VALUES (NULL, '$colums[1]', '$colums[2]', '$colums[4]', '$colums[5]', '$colums[6]', '$colums[7]', '$colums[8]', '$colums[9]', '$colums[10]', '$colums[11]', '$colums[12]', '$colums[13]', '$colums[14]', '$colums[15]', '$colums[16]', '$colums[17]', '$colums[18]', '$colums[19]', '$colums[20]', '$colums[21]', '$colums[22]', '$colums[23]', '$colums[24]', '$colums[25]')";
					mysql_query($sql);
					$customerid = mysql_insert_id();

					//Update MAC
					$modemmac = $colums[1];
					$modemtype = $colums[2];  
					$result = mysql_query("SELECT id FROM `mmst_helper`.`macs` WHERE `macs`.`mac` = '$modemmac' ORDER BY `macs`.`id` DESC LIMIT 1");
					if(mysql_num_rows($result)>0){
						$macid = mysql_fetch_object($result)->id;
						$modemtypeid = mactypefinder($modemtype,$macstype);
						mysql_query("UPDATE `mmst_helper`.`macs` SET `mactype_id` = '$modemtypeid' WHERE `macs`.`id` = '$macid' LIMIT 1");
					}
					//Assign MAC to customer
					mysql_query("INSERT INTO `mmst_helper`.`customershasmacs` (`id`, `customer_id`, `mac_id`) VALUES (NULL, '$customerid', '$macid')");
				}
				else {
					//Update MAC and add Blacklist entry
					$modemmac = $colums[1]; 
					$result = mysql_query("SELECT id FROM `mmst_helper`.`macs` WHERE `macs`.`mac` = '$modemmac' ORDER BY `macs`.`id` DESC LIMIT 1");
					$macid = mysql_fetch_object($result)->id;
					
					$macstatus = 26; // -> exception
					mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
					
					$problemid = 9; // -> Customer Informations not found
					$blackliststatus = 28; // -> open
			    	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");
				}
				if($modemtypeid == 10 || $modemtypeid  == 11 || $modemtypeid  == 31) {
					
					$problemid = 14; // -> Wrong CPE Type no swap needed
					$blackliststatus = 30; // -> skipped
			    	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '$macid', '$problemid', '$blackliststatus')");	
				
			    	$macstatus = 27; // -> skipped
					mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
					
				}
			}	
			
			// Insert product data
			if($colums[0] == "INSTALLED PRODUCTS"){
			    $countcolums = count($colums); $r = 2;
			    
			    while($r < $countcolums){
			    	if(strlen($colums[$r]) > 1){
			        	$accountnumber = $colums[$r]; 	$r++;  
			        	$instance = $colums[$r]; 		$r++;    
						$service = $colums[$r]; 		$r++; 	
						$family = $colums[$r]; 			$r = $r+2;
						$cperef = $colums[$r];  	    $r = $r+2;
						$partnumber = $colums[$r]; 		$r++; 
						$partdescription = $colums[$r]; $r++; 
					
						$check = mysql_query("SELECT COUNT(*) as count FROM customers WHERE account_number = '$accountnumber'");
						$countcustomer = mysql_fetch_object($check)->count;
						if($countcustomer > 0) {
							$result = mysql_query("SELECT id FROM customers WHERE account_number = '$accountnumber'");
							$customerid = mysql_fetch_object($result)->id;
							$products = createdproductarray();
							$productid = productfinder($partnumber,$products);
					
							if($productid != 0) {
								mysql_query("INSERT INTO `mmst_helper`.`productshascustomers` (`id`, `product_id`, `customer_id`, `cperef`) VALUES (NULL, '$productid', '$customerid','$cperef')");
							}
							else {  
								if(strlen($instance) > 3) {
									mysql_query("INSERT INTO `mmst_helper`.`products` (`id`, `instance`, `service`, `family`, `partnumber`, `partdescription`) VALUES (NULL, '$instance', '$service', '$family', '$partnumber', '$partdescription')");
									$productid = mysql_insert_id();
									mysql_query("INSERT INTO `mmst_helper`.`productshascustomers` (`id`, `product_id`, `customer_id`, `cperef`) VALUES (NULL, '$productid', '$customerid','$cperef')");
								}
							}
						}
			    	}
			    	else{
			    		$r = $countcolums;	
			    	}
			    }
			}

			// Insert Partrequests data
			if($colums[0] == "OPEN PART REQUEST"){
				$text = "NO OPEN PR";
				if(strpos($colums[2],$text) === false){
					$countcolums = count($colums); $r = 2;
			 		while($r < $countcolums){
			        	if(strlen($colums[$r]) > 1){
			 				$pr = $colums[$r]; 				$r++;
			        		$nextdate = $colums[$r]; 		$r = $r+2;      	
							$status = $colums[$r]; 			$r++; 
							$type = $colums[$r]; 			$r++;
							$accountnumber = $colums[$r]; 	$r++;
					
							$check = mysql_query("SELECT COUNT(*) as count FROM customers WHERE account_number = '$accountnumber'");
							$countcustomer = mysql_fetch_object($check)->count;
							if($countcustomer > 0) {
					    		$result = mysql_query("SELECT id FROM customers WHERE account_number = '$accountnumber'");
								$customerid = mysql_fetch_object($result)->id;
						
								mysql_query("INSERT INTO `mmst_helper`.`pendingorders` (`id`, `pr`, `type`, `status`, `nextdate`) VALUES (NULL, '$pr', '$type', '$status', '$nextdate')");
								$prid = mysql_insert_id();
								mysql_query("INSERT INTO `mmst_helper`.`pendingordershascustomers` (`id`, `pendingorder_id`, `customer_id`) VALUES (NULL, '$prid', '$customerid')");
							}
			        	}
			        	else {
			        		$r = $countcolums;
			        	}
			 		}
				}
			}
						
			
			if($colums[0] == "INSTALLED CPE CHECK"){  
			    $mac = $colums[1];
			    $result = mysql_query("SELECT id FROM macs WHERE mac = '$mac'");
			    $macid = mysql_fetch_object($result)->id;
			    
			    $macstatus = 26; // -> exception
				mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
				
				$problemid = 7; // -> MAC not or incorrect installed in clarify
				$blackliststatus = 28; // -> open
			    mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '$macid', '$problemid', '$blackliststatus')");	
					
			}				
			$x++;
		}
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '3', NOW())");
		$this->redirect(array('controller' => 'massswap', 'action' => 'step4', '?' => array('runid' => $runid)));
	}
	
	
	// Compare files from step2 and step3
	function step4(){
	    // Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		 // check if step is already passed
	    if(($this->Functions->checkstatus($runid,4) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step5', '?' => array('runid' => $runid)));
	    }
	    else {
			$result = mysql_query("SELECT m.id FROM macs AS m,runshasmacs AS r WHERE m.id = r.mac_id AND r.run_id = '$runid' AND NOT EXISTS (SELECT * FROM customers AS c,customershasmacs AS chm WHERE m.mac = c.mac_address AND c.id = chm.customer_id AND chm.mac_id = m.id) AND NOT EXISTS (SELECT * FROM blacklists AS b WHERE m.id = b.mac_id)");
	    	if(mysql_num_rows($result) > 0){
	    		while($row = mysql_fetch_array($result)){
	    			$macid = $row["id"];
	    			$macstatus = 26; // -> exception
					mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
				
					$problemid = 1; // -> Missing MAC
					$blackliststatus = 28; // -> open
			    	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");
	    		}
	    	}
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '4', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step23', '?' => array('runid' => $runid)));
	    }  	
	}
	
	// Excluded Partnernetworks
	function step23() {
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		 // check if step is already passed
	    if(($this->Functions->checkstatus($runid,46) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step5', '?' => array('runid' => $runid)));
	    }
	    else {
	    	if($this->Functions->checkdtv($runid) == 0) { $service = "HS/DP"; } else { $service = "DTV"; }
	    	$result = mysql_query("SELECT rhm.mac_id AS id FROM runshasmacs AS rhm, customers AS c, customershasmacs AS chm, blockingcities AS bc WHERE c.id = chm.customer_id AND chm.mac_id = rhm.mac_id AND c.inst_zip = bc.plz AND rhm.run_id = '$runid' AND bc.service = '$service' AND NOT EXISTS(SELECT * FROM blacklists AS b WHERE rhm.mac_id = b.mac_id)");
			if(mysql_num_rows($result) > 0){
	    		while($row = mysql_fetch_array($result)){
	    			$macid = $row["id"];
	    			$macstatus = 27; // -> skipped
					mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
				
					$problemid = 15; // -> Excluded Partnernetworks
					$blackliststatus = 30; // -> skipped
			    	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");	
	    		}
	    	}
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '46', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step5', '?' => array('runid' => $runid))); 
	    } 		
	}
	
	
	
	// Breakdown Bad Payers
	function step5(){
	    // Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		 // check if step is already passed
	    if(($this->Functions->checkstatus($runid,5) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step6', '?' => array('runid' => $runid)));
	    }
	    else {
			$text = "Bad Payer";
	        $result = mysql_query("SELECT m.id FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND rhm.run_id = '$runid' AND c.contact_role = '$text' AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id )");
			if(mysql_num_rows($result) > 0){
	    		while($row = mysql_fetch_array($result)){
	    			$macid = $row["id"];
	    			$macstatus = 27; // -> skipped
					mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
				
					$problemid = 2; // -> Bad Payers
					$blackliststatus = 30; // -> skipped
			    	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");	
	    		}
	    	}
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '5', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step7', '?' => array('runid' => $runid))); 
	    } 	
	} 
	
	

	// Breakdown customer with 2nd line
	function step7(){
	    // Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }

		 // check if step is already passed
	    if((($this->Functions->checkstatus($runid,7) != 0) && ($redo == 0)) || ($this->Functions->checkdtv($runid) != 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step6', '?' => array('runid' => $runid)));
	    }
	    else {
			$product = "PT - 1014";
			$result = mysql_query("SELECT DISTINCT m.id,c.serial_no,account_number FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm, products AS p, productshascustomers AS phc WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND c.id = phc.customer_id AND p.id = phc.product_id AND rhm.run_id = '$runid' AND p.instance = '$product' AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id )");
			if(mysql_num_rows($result) > 0){
	    		while($row = mysql_fetch_array($result)){
	    			$macid = $row["id"];
	    			$serial = $row["serial_no"];
	    			$account = $row["account_number"];
	    		
	    			// DB Abfrage auf ORACLE   		
	    			$return = $this->requestAction(array('controller' => 'oracles', 'action' => 'findxconnect'), array('pass' => array($account)));

	    			
	    			if($return != 0) {
	    				$macstatus = 26; // -> exception
						mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
						
			    		mysql_query("INSERT INTO `mmst_helper`.`manualmoves` (`id`, `date`, `mac_id`) VALUES (NULL, NOW(),'$macid')");
			    		$mmid = mysql_insert_id();
			    		$mmstatus = 48; // -> Ready for processing
			    		mysql_query("INSERT INTO `mmst_helper`.`manualmoveshasstatuses` (`id`, `date`, `manualmove_id`, `status_id`) VALUES (NULL, NOW(), '$mmid', '$mmstatus')");
	    			}
	    		}
	    	}
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '7', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step6', '?' => array('runid' => $runid)));
	    }
	} 
	
	
	// Breakdown pending orders
	function step6(){
	    // Import Vars from URL
		$runid = $this->params['url']['runid']; 
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; } 

		 // check if step is already passed
	    if(($this->Functions->checkstatus($runid,6) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step21', '?' => array('runid' => $runid)));
	    }
	    else {
			$result = mysql_query("SELECT DISTINCT m.id FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm, pendingordershascustomers AS phc WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND c.id = phc.customer_id AND rhm.run_id = '$runid' AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id )");
			if(mysql_num_rows($result) > 0){
	    		while($row = mysql_fetch_array($result)){
	    			$macid = $row["id"];
	    			$macstatus = 26; // -> exception
					mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
				
					$problemid = 3; // -> Pending orders
					$blackliststatus = 28; // -> open
			    	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");	
	    		}
	    	}
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '6', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step21', '?' => array('runid' => $runid)));
	    }
	} 
	
	
	// Breakdown customer with 2 modems in the same run
	function step21(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }

		 // check if step is already passed
	    if(($this->Functions->checkstatus($runid,23) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step8', '?' => array('runid' => $runid)));
	    }
	    else {
			$result = mysql_query("SELECT chm.mac_id FROM customers c, customershasmacs chm, macs m WHERE c.id = chm.customer_id AND chm.mac_id = m.id AND m.status_id = 24 AND account_number IN (SELECT account_number FROM customers c, customershasmacs chm, runshasmacs rhm, macs m WHERE c.id = chm.customer_id AND chm.mac_id = rhm.mac_id AND rhm.run_id = '$runid' AND chm.mac_id = m.id GROUP BY account_number HAVING COUNT(account_number) > 1)");
	    	WHILE($rows = mysql_fetch_array($result)) {
	    		$macid = $rows["mac_id"];
	    		$macstatus = 26; // -> exception
				mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
				$problemid = 13; // -> Customer with more then one Modem
				$blackliststatus = 28; // -> open
			    mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");	
	    	}
	    	
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '23', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step24', '?' => array('runid' => $runid)));
	    }	
	}
	
	// breakdown D2 CAS Customers
	function step24(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }

		// check if step is already passed
		if((($this->Functions->checkstatus($runid,45) != 0) && ($redo == 0)) || ($this->Functions->checkdtv($runid) == 0)) {
			$this->redirect(array('controller' => 'massswap', 'action' => 'step8', '?' => array('runid' => $runid)));
		}
		else {
			$result = mysql_query("SELECT m.id, c.serial_no FROM runs r, runshasmacs rhm, macs m, customershasmacs chm, customers c, productshascustomers phc  WHERE r.id = '$runid' AND r.id = rhm.run_id AND rhm.mac_id = m.id AND m.id = chm.mac_id AND chm.customer_id = c.id AND phc.customer_id = c.id AND phc.cperef = 'DMM - SC2' GROUP BY c.account_number");
			WHILE($row = mysql_fetch_array($result)){
				$macid = $row["id"];
				$serial = $row["serial_no"];
				
				// DB Abfrage auf ORACLE
				$instance = $this->requestAction(array('controller' => 'oracles', 'action' => 'findInstance'), array('pass' => array($serial)));
				IF($instance['1']['TSP']['instance_name'] == 'DMM - SC2'){
				    $macstatus = 26; // -> exception
					mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
					$problemid = 16; // -> Customer on D2 CAS provisioned
					$blackliststatus = 28; // -> open
					mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");
				}	
			}
		}
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '45', NOW())");
		$this->redirect(array('controller' => 'massswap', 'action' => 'step8', '?' => array('runid' => $runid)));	
	}
	
	
	
	// Adress corrections
	function step8(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		$this->set(compact('runid'));  
		
		 // check if step is already passed
	    if(($this->Functions->checkstatus($runid,8) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step9', '?' => array('runid' => $runid)));
	    }
	    else {
			$this->loadModel('Customer');
			$result = $this->Customer->query("SELECT c.id, c.account_number, c.inst_first_name, c.inst_last_name, c.salutation, c.company_name, c.po_box, c.bill_street, c.bill_house_no, c.bill_zip, c.bill_city FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND rhm.run_id = '$runid' AND (bill_house_no = '' OR bill_street = '' OR bill_zip = '' OR bill_city = '' OR bill_street LIKE '%Post%' OR bill_street LIKE '%###%' OR bill_house_no = '0' OR bill_city = '0' OR inst_first_name LIKE '%#%' OR inst_last_name LIKE '%#%') AND adrressok <> 1 AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id)");
			if(!empty($result)){
				$this->set(compact('result'));
				foreach($result AS $r) {
					$customerid = $r['c']['id'];
					$check = mysql_query("SELECT COUNT(*) AS count FROM adresscorrections WHERE customer_id = '$customerid'");
					$anz = mysql_fetch_object($check)->count;
					if($anz == 0){
						$addresstatus = 38; // -> open
						mysql_query("INSERT INTO `mmst_helper`.`adresscorrections` (`id`, `status_id`, `customer_id`) VALUES (NULL, '$addresstatus', '$customerid')");
					}
				}
			}
			else {
				mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '8', NOW())");
				$this->redirect(array('controller' => 'massswap', 'action' => 'step9', '?' => array('runid' => $runid)));	
			}
	    }
	} 
		
	
	// Export Intrum file
	function step9(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		 
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,9) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step10', '?' => array('runid' => $runid)));
	    }
	    else {
			$this->set(compact('runid'));
	    }
	}
	
	
	// Export Intrum file
	function step9action(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '9', NOW())");
		$this->redirect(array('controller' => 'massswap', 'action' => 'step10', '?' => array('runid' => $runid)));
	}

	
	// Import Intrum file
	function step10(){
		
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
	    $this->set(compact('runid'));
	     
	    // check if step is already passed
	    if(($this->Functions->checkstatus($runid,10) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step11', '?' => array('runid' => $runid)));
	    }
	    else {
	    	if (!empty($this->data) && is_uploaded_file($this->data['Myfile']['upload']['tmp_name'])) {
            	$fileData = fread(fopen($this->data['Myfile']['upload']['tmp_name'], "r"),$this->data['Myfile']['upload']['size']);
            
            	$this->data['Myfile']['name'] = $this->data['Myfile']['upload']['name'];
            	$this->data['Myfile']['type'] = $this->data['Myfile']['upload']['type'];
            	$this->data['Myfile']['size'] = $this->data['Myfile']['upload']['size'];
            	$this->data['Myfile']['data'] = $fileData;

            	$this->loadModel('Myfile');
            	$this->Myfile->save($this->data);
            	$fileid = $this->Myfile->id;
          
            	mysql_query("INSERT INTO `mmst_helper`.`runshasfiles` (`id`, `run_id`, `file_id`) VALUES (NULL, '$runid', '$fileid')");
	        	$this->Session->setFlash(__('Fileuplado has been saved', true));
            	$this->redirect(array('controller' => 'massswap', 'action' => 'step10action', '?' => array('runid' => $runid, 'fileid' => $fileid)));
	    	}
			else {
		   		$this->Session->setFlash(__('Uploaded file could not be saved. Please, try again.', true));
			}	
	    }
	} 
	
	
	// Import Intrum file
	function step10action(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		$fileid = $this->params['url']['fileid'];
		
		$this->loadModel('Myfile');
		$this->data = $this->Myfile->findById($fileid);
		$filedata = $this->data['Myfile']['data'];
		$rows = explode("\n",$filedata);
		$count = count($rows);		
		
		$x = 1; // first line is the header -> skipping
		// Update credit rating
		while($x < $count) {
			$colums = explode(";",$rows[$x]); 
			$count2 = count($colums);
			if($count2 > 1) {
				$customerid = $colums[0];
				$rating = $colums[9];
				mysql_query("UPDATE `mmst_helper`.`customers` SET `credit_rating` = '$rating' WHERE `customers`.`id` = '$customerid'");
			}
			$x++;
		}
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '10', NOW())");
		$this->redirect(array('controller' => 'massswap', 'action' => 'step11', '?' => array('runid' => $runid)));	
	} 
	
	
	// Breakdown creditrating
	function step11(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }

		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,11) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step12', '?' => array('runid' => $runid)));
	    }
	    else {
			$result = mysql_query("SELECT m.id AS id FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND rhm.run_id = '$runid' AND c.credit_rating >= 5 AND NOT EXISTS (SELECT * FROM blacklists AS b WHERE m.id = b.mac_id)");
			while($rows = mysql_fetch_array($result)){
				$macid = $rows["id"];
				$macstatus = 26; // -> exception
				mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
				$problemid = 5; // -> Negativ Creditrating
				$blackliststatus = 28; // -> open
			    mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");
			    	
			}
			mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '11', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step12', '?' => array('runid' => $runid)));
	    }	
	}
	
	
	// Export schmied & fehrfile
	function step12(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,12) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step13', '?' => array('runid' => $runid)));
	    }
	    else {
			$result = mysql_query("SELECT project_id pid FROM runs WHERE id = '$runid'");
			$pid = mysql_fetch_object($result)->pid;
	    	$this->set(compact('runid','pid'));	
	    }
	} 
	
	
	// Export schmied & fehrfile
	function step12action(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		 
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '12', NOW())");
		$this->redirect(array('controller' => 'massswap', 'action' => 'step13', '?' => array('runid' => $runid)));
	}

	
	// Export CTSP file
	function step13(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,13) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step14', '?' => array('runid' => $runid)));
	    }
	    else {
			$this->set(compact('runid'));
	    }	  	
	} 
	
	
	// Export CTSP file
	function step13action(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '13', NOW())");
		$this->redirect(array('controller' => 'massswap', 'action' => 'step14', '?' => array('runid' => $runid)));
	}
	
	
	// Export letterinteraction file
	function step14(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,14) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step15', '?' => array('runid' => $runid)));
	    }
	    else {
			$result = mysql_query("SELECT project_id pid FROM runs WHERE id = '$runid'");
			$pid = mysql_fetch_object($result)->pid;
	    	$this->set(compact('runid','pid'));
	    }	  	
	} 
	
	
	// Export letterinteraction file
	function step14action(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '14', NOW())");
		$this->redirect(array('controller' => 'massswap', 'action' => 'step15', '?' => array('runid' => $runid)));
	}
	
	
	// Blockers 72h till reaching step12
	function step15(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,15) != 0)&& ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step16', '?' => array('runid' => $runid)));
	    }
	    else {
			$datedb = strtotime($this->Functions->getstatusdate($runid,14));
			$datedb2 = mktime(6, 0, 0, date("m",($datedb)), date("d",($datedb)), date("Y",($datedb)));
			
			
			$weekdaynumber = date("N",$datedb2);
			$wd = $weekdaynumber;
			
			IF($wd == 1) { $exitdate = $datedb2 + 60*60*24*3; } // Montag
			IF($wd == 2) { $exitdate = $datedb2 + 60*60*24*3; } // Dienstag
			IF($wd == 3) { $exitdate = $datedb2 + 60*60*24*5; } // Mittwoch
			IF($wd == 4) { $exitdate = $datedb2 + 60*60*24*5; } // Donnerstag
			IF($wd == 5) { $exitdate = $datedb2 + 60*60*24*5; } // Freitag
			IF($wd == 6) { $exitdate = $datedb2 + 60*60*24*5; } // Samstag
			IF($wd == 7) { $exitdate = $datedb2 + 60*60*24*4; } // Sonntag
			
			$now = time();
			if($now > $exitdate) {
				mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '15', NOW())");
				$this->redirect(array('controller' => 'massswap', 'action' => 'step16', '?' => array('runid' => $runid)));
			}
			else {
				$this->set(compact('exitdate'));
			}
	    }	
	} 
	
	//2nd Breakdown of pending orders
	function step16(){

	    // Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		 // check if step is already passed
	    if(($this->Functions->checkstatus($runid,16) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step17', '?' => array('runid' => $runid)));
	    }
	    else {
			$result = mysql_query("SELECT c.account_number, m.id FROM macs AS m, runshasmacs AS rhm, customers AS c, customershasmacs AS chm WHERE m.id = rhm.mac_id AND c.id = chm.customer_id AND m.id = chm.mac_id AND rhm.run_id = '$runid' AND NOT EXISTS( SELECT * FROM blacklists AS b WHERE m.id = b.mac_id )");
			if(mysql_num_rows($result) > 0){
	    		while($row = mysql_fetch_array($result)){
	    			$macid = $row["id"];
	    			$account = $row["account_number"];
	    			
	    			// DB Abfrage auf OMON Database 
	    			$counter = $this->requestAction(array('controller' => 'omons', 'action' => 'findPR'), array('pass' => array($account)));
	    			if($counter > 0) {
	    				$macstatus = 26; // -> exception
						mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
						$problemid = 3; // -> Pending Orders
						$blackliststatus = 28; // -> open
			    		mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");
	    			}
	    		}
	    	}
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '16', NOW())");
			$this->redirect(array('controller' => 'massswap', 'action' => 'step6', '?' => array('runid' => $runid))); 
	    } 
	}
	
	
	// MAC Adress export from Taylortool
	function step17(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		 
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,17) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step18', '?' => array('runid' => $runid)));
	    }
	    else {
	    	$this->set(compact('runid'));
	    }  	
	}
	
	
	// MAC Adress export from Taylortool
	function step17action(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '17', NOW())");
		$this->redirect(array('controller' => 'runs', 'action' => 'edit2', '?' => array('runid' => $runid)));
	}

	
	// Update run with file and batch id
	function step18(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }

		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,18) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step19', '?' => array('runid' => $runid)));
	    }
	    else {
	        $this->redirect(array('controller' => 'runs', 'action' => 'edit2', '?' => array('runid' => $runid)));
	    } 
	} 
	
	/*
	// Data import is done over DMP View with daily cron
	// Out-quoted at 14.11.2011
	
	// load mms logfile
	function step19(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		$this->set(compact('runid'));
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,19) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step20', '?' => array('runid' => $runid)));
	    }
	    else {
	    	if (!empty($this->data) && is_uploaded_file($this->data['Myfile']['upload']['tmp_name'])) {
            	$fileData = fread(fopen($this->data['Myfile']['upload']['tmp_name'], "r"),$this->data['Myfile']['upload']['size']);
            
            	$this->data['Myfile']['name'] = $this->data['Myfile']['upload']['name'];
            	$this->data['Myfile']['type'] = $this->data['Myfile']['upload']['type'];
            	$this->data['Myfile']['size'] = $this->data['Myfile']['upload']['size'];
            	$this->data['Myfile']['data'] = $fileData;

            	$this->loadModel('Myfile');
            	$this->Myfile->save($this->data);
            	$fileid = $this->Myfile->id;
          
            	mysql_query("INSERT INTO `mmst_helper`.`runshasfiles` (`id`, `run_id`, `file_id`) VALUES (NULL, '$runid', '$fileid')");
	        	$this->Session->setFlash(__('Fileuplado has been saved', true));
            	$this->redirect(array('controller' => 'massswap', 'action' => 'step19action', '?' => array('runid' => $runid, 'fileid' => $fileid)));
	    	}
			else {
		   		$this->Session->setFlash(__('Uploaded file could not be saved. Please, try again.', true));
			}
	    }
	} 
	*/
	
	
	// load weborders
	function step19(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
	    if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,19) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step20', '?' => array('runid' => $runid)));
	    }
	    else {
	    	$datedb = strtotime($this->Functions->getBatchStartDate($runid));
			$datedb2 = mktime(8, 0, 0, date("m",($datedb)), date("d",($datedb)), date("Y",($datedb)));
			
			
			$weekdaynumber = date("N",$datedb2);
			$wd = $weekdaynumber;
			
			IF($wd == 1) { $exitdate = $datedb2 + 60*60*24*1; } // Montag
			IF($wd == 2) { $exitdate = $datedb2 + 60*60*24*1; } // Dienstag
			IF($wd == 3) { $exitdate = $datedb2 + 60*60*24*1; } // Mittwoch
			IF($wd == 4) { $exitdate = $datedb2 + 60*60*24*1; } // Donnerstag
			IF($wd == 5) { $exitdate = $datedb2 + 60*60*24*1; } // Freitag
			IF($wd == 6) { $exitdate = $datedb2 + 60*60*24*1; } // Samstag
			IF($wd == 7) { $exitdate = $datedb2 + 60*60*24*1; } // Sonntag
			
			$now = time();
			
			if($now < $exitdate) {
				$this->set(compact('exitdate'));
			}
			else {
	        	$resultbatch = mysql_query("SELECT batchid FROM runs WHERE id = '$runid'");
	    		$batchid = mysql_fetch_object($resultbatch)->batchid;
	    		$result = mysql_query("SELECT * FROM massswapdata WHERE batchid = '$batchid'");
	    		if(mysql_num_rows($result) != 0){
	    			WHILE($row = mysql_fetch_array($result)){
	    				$weborderid = $row["weborderid"];
	    		
	    				if($weborderid != '0') {
	    					mysql_query("INSERT INTO `mmst_helper`.`weborders` (`id`, `weborderid`, `run_id`) VALUES (NULL, '$weborderid', '$runid')");
	    				}
	    				else {
	    					$mac = $row["macaddress"];
	    					$resultmac = mysql_query("SELECT id FROM macs WHERE mac = '$mac'");
	    					$macid = mysql_fetch_object($resultmac)->id;
	    					$blackliststatus = 28; // -> open
        					$blacklisterror = 12; // -> Error in MMS Tool
        					mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$blacklisterror', '$blackliststatus')");
        					$macstatus = 26; // -> exception
							mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");	
	    				}
	    			}
	    			mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '19', NOW())");
					$this->redirect(array('controller' => 'massswap', 'action' => 'step20', '?' => array('runid' => $runid)));
	    		}
	    		else {
	    			$this->redirect(array('controller' => 'massswap', 'action' => 'dmperror'));
	    		}
			}
	    }
	} 
	
	
	// Assign prs and cases
	function step20(){
	 	// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
			
		
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,20) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step22', '?' => array('runid' => $runid)));
	    }
	    else {
	    	$result = mysql_query("SELECT weborderid,id FROM weborders WHERE run_id = '$runid'");
	    	WHILE($rows = mysql_fetch_array($result)){
	    		// DB Abfrage auf ORACLE 
	    		$weborderid = $rows["weborderid"];
	    		$wid = $rows["id"];
	    		$details = $this->requestAction(array('controller' => 'oracles', 'action' => 'findDetails'), array('pass' => array($weborderid)));
	    		   		
	    		if(empty($details)){
	    			$weborderstatus = 40; // -> open
	    			mysql_query("INSERT INTO `mmst_helper`.`weborderexceptions` (`id`, `weborder_id`, `status_id`) VALUES (NULL, '$wid', '$weborderstatus');");
	    		}
	    		else {
	    			$account = $details['0']['0']['CUST_ID']; 
	    			if(strlen($details['0']['0']['CASE_ID']) > 2) { $caseid = $details['0']['0']['CASE_ID']; } else { $caseid = 0; }
	    			if(strlen($details['0']['0']['PART_REQUEST_ID']) > 2) { $prid = $details['0']['0']['PART_REQUEST_ID']; } else { $prid = 0; }
	    		
	    			$resultmac = mysql_query("SELECT chm.mac_id AS macid FROM customers AS c, customershasmacs AS chm, runshasmacs AS rhm WHERE c.account_number = '$account' AND chm.customer_id = c.id AND chm.mac_id = rhm.mac_id AND rhm.run_id = '$runid'");
					if(mysql_num_rows($resultmac) > 0) {
						$macid = mysql_fetch_object($resultmac)->macid;
					}
					else {
						$macid = 0;
					}
	    		
	    			if($caseid != '0'){
	    				$exeptionstatus = 31; // -> open
	    				mysql_query("INSERT INTO `mmst_helper`.`exceptionscases` (`id`, `caseid`, `status_id`, `weborder_id`, `mac_id`) VALUES (NULL, '$caseid', '$exeptionstatus', '$wid', '$macid');");
						$macstatus = 26; // -> exception
						mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");
						$problemid = 10; // -> Exception Case
						$blackliststatus = 28; // -> open
			    		mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$problemid', '$blackliststatus');");
	    			}
	    			if($prid != '0'){
	    				mysql_query("INSERT INTO `mmst_helper`.`partrequests` (`id`, `headernumber`, `detailnumber`, `xto`, `xfrom`, `xtodate`, `trackingnumber`, `weborder_id`, `mac_id`) VALUES (NULL, '$prid', NULL, NULL, NULL, NULL, NULL, '$wid', '$macid')");
	    			}
	    		}
	    	}
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '20', NOW())");
	    	$this->redirect(array('controller' => 'massswap', 'action' => 'step20errors', '?' => array('runid' => $runid)));	
	    } 
	}
	 
	function step20errors(){
		// Import Vars from URL
		$runid = $this->params['url']['runid'];
		
		$result = mysql_query("SELECT COUNT(*) count FROM weborderexceptions we, weborders w WHERE w.id = we.weborder_id AND w.run_id = '$runid' AND weborderid NOT REGEXP '^M' AND weborderid NOT REGEXP '^P'");
		$count = mysql_fetch_object($result)->count;
		if($count > 0) {
			$result2 = mysql_query("SELECT weborderid FROM weborderexceptions we, weborders w WHERE w.id = we.weborder_id AND w.run_id = '$runid' AND weborderid NOT REGEXP '^M' AND weborderid NOT REGEXP '^P'");
		    $this->set(compact('runid','result2'));	
		}
		else {
			mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '44', NOW())");
	    	mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '21', NOW())");
			$this->redirect(array('controller' => 'runs', 'action' => 'overview'));	
		}
	}
	
	function step20action(){
		$runid = $fielddata = $this->data['Massswap']['runid'];
        $fielddata = $this->data['Massswap'];
        $fieldcount = count($this->data['Massswap'])-1;

        $i = 1;
        WHILE($i <= $fieldcount) {
        	$name = "field".$i;
        	
        	$mac = $fielddata[$name];

        	$result = mysql_query("SELECT id FROM macs WHERE mac = '$mac'");
        	$macid = mysql_fetch_object($result)->id;
        	
        	$result = mysql_query("SELECT webexceptions FROM configs WHERE id = 1");
			$counter = mysql_fetch_object($result)->webexceptions;
			$zahl = 300000000;
			$webnummer = $zahl+$counter;
			$weborder = "ERR".$webnummer;
			$counter++;
			
			mysql_query("INSERT INTO `mmst_helper`.`weborders` (`id`, `weborderid`, `run_id`) VALUES (NULL, '$weborder', '$runid')");
			$weborderid = mysql_insert_id();
        	mysql_query("UPDATE `mmst_helper`.`configs` SET `webexceptions` = '$counter' WHERE `configs`.`id` = 1");

        	$blackliststatus = 28; // -> open
        	$blacklisterror = 11; // -> Weborder lost between ALSB and CYL
        	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$blacklisterror', '$blackliststatus');");
        	
        	$macstatus = 26; // -> exception
			mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");	
        	$i++;
        }
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '44', NOW())");
	    mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '21', NOW())");
		$this->redirect(array('controller' => 'runs', 'action' => 'overview'));			
	}

	/*
	// Data import is done over DMP View with daily cron steps not needed anymore
	// Out-quoted at 14.11.2011

	// MMS Errors
	function step22(){
	 	// Import Vars from URL
		$runid = $this->params['url']['runid'];
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
			
		// check if step is already passed
	    if(($this->Functions->checkstatus($runid,24) != 0) && ($redo == 0)) {
	    	$this->redirect(array('controller' => 'runs', 'action' => 'overview'));
	    }
	    
	    $this->set(compact('runid'));
	}
	
	
	function step22form() {
		$this->layout = null;
        $this->autoLayout = false;
		$count = $this->params['form']['count'];
		$runid = $this->params['form']['runid'];
		$this->set(compact('count','runid'));
	}
	
	
	function step22skip() {
		$runid = $this->params['url']['runid'];
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '44', NOW())");
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '21', NOW())");
		$this->redirect(array('controller' => 'runs', 'action' => 'overview'));	
	}
	
	
	function step22action() {
		$runid = $fielddata = $this->data['Massswap']['runid'];
        $fielddata = $this->data['Massswap'];
        $fieldcount = count($this->data['Massswap'])-1;

        $i = 1;
        WHILE($i <= $fieldcount) {
        	$name = "field".$i;
        	
        	$mac = $fielddata[$name];

        	$result = mysql_query("SELECT id FROM macs WHERE mac = '$mac'");
        	$macid = mysql_fetch_object($result)->id;
        	
        	$result = mysql_query("SELECT webexceptions FROM configs WHERE id = 1");
			$counter = mysql_fetch_object($result)->webexceptions;
			$zahl = 300000000;
			$webnummer = $zahl+$counter;
			$weborder = "ERR".$webnummer;
			$counter++;
			
			mysql_query("INSERT INTO `mmst_helper`.`weborders` (`id`, `weborderid`, `run_id`) VALUES (NULL, '$weborder', '$runid')");
			$weborderid = mysql_insert_id();
        	mysql_query("UPDATE `mmst_helper`.`configs` SET `webexceptions` = '$counter' WHERE `configs`.`id` = 1");

        	$blackliststatus = 28; // -> open
        	$blacklisterror = 12; // -> Error in MMS Tool
        	mysql_query("INSERT INTO `mmst_helper`.`blacklists` (`id`, `date`, `letter`, `interaction`, `ctsp`, `comment`, `futuredated`, `mac_id`, `problem_id`, `status_id`) VALUES (NULL, NOW(), '0', '0', '0', NULL, NULL, '$macid', '$blacklisterror', '$blackliststatus');");
        	
        	$macstatus = 26; // -> exception
			mysql_query("UPDATE `mmst_helper`.`macs` SET `status_id` = '$macstatus' WHERE `macs`.`id` = '$macid'");	
        	$i++;
        }
       
		mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '44', NOW())");
	    mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '21', NOW())");
		$this->redirect(array('controller' => 'runs', 'action' => 'overview'));			
	}
	*/
}
?>
